﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;

namespace AssetTrackDemo
{
    static class DataHelper
    {
        static public string ConnectionString = "Data Source=\\Program Files\\AssetTrackDemo\\AssetTrackDB.sdf";

        static public bool InsertMaterial(string Name, string TID)
        {
            try
            {
                using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                {
                    using (SqlCeCommand cmdInsert = connection.CreateCommand())
                    {
                        connection.Open();
                        cmdInsert.CommandText = "INSERT MatMast(Name, TagId) Values ('" + Name + "', '" + TID + "')";
                        cmdInsert.ExecuteNonQuery();
                        connection.Close();
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        static public string SelectItem(string TagID)
        {
            string Name;
            try
            {
                using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                {
                    using (SqlCeCommand cmdSelect = connection.CreateCommand())
                    {
                        cmdSelect.CommandText = "SELECT Name FROM tblTxn WHERE TID = '" + TagID + "'";
                        connection.Open();
                        Name = Convert.ToString(cmdSelect.ExecuteScalar());
                        connection.Close();
                    }
                }
                return Name;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        static public List<Material> SelectAllMaterial()
        {
            try
            {
                List<Material> lstMat = new List<Material>();
                using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                {
                    using (SqlCeCommand cmdSelect = connection.CreateCommand())
                    {
                        cmdSelect.CommandText = "SELECT * FROM MatMast WHERE IsActive = 1";
                        connection.Open();
                        SqlCeDataReader dr = cmdSelect.ExecuteReader();
                        while (dr.Read())
                        {
                            lstMat.Add(new Material(Convert.ToInt32(dr["ID"]), Convert.ToString(dr["Name"]), Convert.ToString(dr["TagId"])));
                        }
                        connection.Close();
                    }
                    return lstMat;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        static public List<Material> ReconciliateMaterial()
        {
            try
            {
                List<Material> lstMat = new List<Material>();
                using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                {
                    using (SqlCeCommand cmdSelect = connection.CreateCommand())
                    {
                        cmdSelect.CommandText = "SELECT * FROM MatMast WHERE IsActive = 1";
                        connection.Open();
                        SqlCeDataReader dr = cmdSelect.ExecuteReader();
                        while (dr.Read())
                        {
                            lstMat.Add(new Material(Convert.ToInt32(dr["ID"]), Convert.ToString(dr["Name"]), Convert.ToString(dr["TagId"])));
                        }
                        connection.Close();
                    }
                    return lstMat;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
